package com.cc.dao.impl;

import com.cc.dao.BaseDao;
import com.cc.dao.UserDao;
import com.cc.entity.EmployeeProfiles;
import com.cc.entity.LeaveApplication;
import com.cc.entity.Page;
import com.cc.entity.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends BaseDao implements UserDao {
    /**
     * 登录
     * @param username
     * @param password
     * @return
     */
    @Override
    public User login(String username, String password) {
        StringBuffer sql = new StringBuffer("select * from users where username=? and password=?");
        ResultSet rs = executeQuery(sql.toString(),username,password);
        User user = null;
        if(rs != null) {
            try {
                while (rs.next()) {
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setRole(rs.getString("role"));
                    user.setEmail(rs.getString("email"));
                    user.setPhone(rs.getString("phone"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closedAll(rs);
            }
        }
        return user;
    }

    @Override
    public EmployeeProfiles getEmployeeById(Integer userId) {
        EmployeeProfiles employeeProfiles = null;
        StringBuffer sql = new StringBuffer();
        sql.append("select * from employee_profiles where user_id=?");
        ResultSet rs = executeQuery(sql.toString(),userId);
        if(rs != null) {
            try {
                while (rs.next()){
                    employeeProfiles = new EmployeeProfiles();
                    employeeProfiles.setId(rs.getInt("id"));
                    employeeProfiles.setUserId(rs.getInt("user_id"));
                    employeeProfiles.setName(rs.getString("name"));
                    employeeProfiles.setPosition(rs.getString("position"));
                    employeeProfiles.setDepartment(rs.getString("department"));
                    employeeProfiles.setHireDate(rs.getDate("hire_date"));
                    employeeProfiles.setLeaveDate(rs.getDate("leave_date"));
                    employeeProfiles.setStatus(rs.getString("status"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return employeeProfiles;
    }

    /**
     * 更新员工信息
     * @param employeeProfiles
     * @return
     */
    @Override
    public int updateEmployeeProfiles(EmployeeProfiles employeeProfiles) {
        StringBuffer sql = new StringBuffer();
        sql.append("update employee_profiles set user_id=?,name=?,position=?,department=?,hire_date=?,leave_date=?,status=? where id=?");
        return executeUpdate(sql.toString(),
                employeeProfiles.getUserId(),
                employeeProfiles.getName(),
                employeeProfiles.getPosition(),
                employeeProfiles.getDepartment(),
                employeeProfiles.getHireDate(),
                employeeProfiles.getLeaveDate(),
                employeeProfiles.getStatus(),
                employeeProfiles.getId());
    }
    /**
     * 获取请假申请
     * @param employeeId
     * @return
     */
    @Override
    public LeaveApplication getLeaveApplicationById(Integer employeeId) {
        LeaveApplication leaveApplication = null;
        StringBuffer sql = new StringBuffer();
        sql.append("select * from leave_applications where employee_id=?");
        ResultSet rs = executeQuery(sql.toString(),employeeId);
        if(rs != null) {
            try {
                while (rs.next()){
                    leaveApplication = new LeaveApplication();
                    leaveApplication.setId(rs.getInt("id"));
                    leaveApplication.setEmployeeId(rs.getInt("employee_id"));
                    leaveApplication.setStartDate(rs.getDate("start_date"));
                    leaveApplication.setEndDate(rs.getDate("end_date"));
                    leaveApplication.setReason(rs.getString("reason"));
                    leaveApplication.setStatus(rs.getString("status"));
                    leaveApplication.setManagerComment(rs.getString("manager_comment"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return leaveApplication;
    }

    @Override
    public Page<EmployeeProfiles> findByPage(String name, Page<EmployeeProfiles> page) {
        // 初始化SQL语句的StringBuilder和参数列表
        StringBuffer sb = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        List<EmployeeProfiles> list = new ArrayList<EmployeeProfiles>();

        // 构建基础查询SQL
        sb.append("select * from employee_profiles");

        // 添加分页查询条件
        sb.append(" limit ?,?");
        params.add((page.getPageNum() - 1) * page.getPageSize());
        params.add(page.getPageSize());

        // 执行查询
        ResultSet rs = executeQuery(sb.toString(), params.toArray());

        // 遍历查询结果，填充学生对象
        try {
            while (rs.next()) {
                EmployeeProfiles employeeProfiles = new EmployeeProfiles();
                employeeProfiles.setId(rs.getInt("id"));
                employeeProfiles.setUserId(rs.getInt("user_id"));
                employeeProfiles.setName(rs.getString("name"));
                employeeProfiles.setPosition(rs.getString("position"));
                employeeProfiles.setDepartment(rs.getString("department"));
                employeeProfiles.setHireDate(rs.getDate("hire_date"));
                employeeProfiles.setLeaveDate(rs.getDate("leave_date"));
                employeeProfiles.setStatus(rs.getString("status"));
                list.add(employeeProfiles);
            }
        } catch (SQLException e) {
            // 将SQL异常包装为运行时异常抛出
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }

        // 设置分页对象的数据和总记录数
        page.setData(list);
        page.setTotal(getTotal(name));
        return page;
    }

    /**
     * 根据用户名获取学生总数
     *
     * 此方法通过执行SQL查询来统计与给定用户名部分匹配的学生总数如果用户名为空或null，则返回所有学生的总数
     *
     * @param name 用户名，用于搜索学生如果为空或null，则查询所有学生的总数
     * @return 学生总数
     */
    @Override
    public int getTotal(String name) {
        // 初始化总数为0
        int total = 0;
        // 初始化参数列表，用于存储SQL查询的参数
        List<Object> params = new ArrayList<Object>();
        // 基础SQL查询语句，用于获取学生总数
        String sql = "select count(*) count from employee_profiles e ";
        // 如果用户名不为空，则在SQL中添加用户名模糊查询条件
        if(name != null && !"".equals(name)){
            sql += " where e.name like CONCAT('%',?,'%')";
            // 将用户名作为参数添加到参数列表中
            params.add(name);
        }
        // 执行SQL查询，返回结果集
        ResultSet rs = executeQuery(sql, params.toArray());
        // 如果结果集不为空，则尝试获取查询结果
        if(rs!=null){
            try {
                // 遍历结果集
                while (rs.next()) {
                    // 返回查询结果中的总数
                    return rs.getInt("count");
                }
            } catch (SQLException e) {
                // 如果捕获到SQLException，则将其包装为RuntimeException并抛出
                throw new RuntimeException(e);
            }
            finally {
                // 关闭结果集
                closedAll(rs);
            }
        }
        // 如果查询无结果或发生异常，则返回初始化的总数0
        return total;
    }

    @Override
    public int addEmployee(EmployeeProfiles employeeProfiles) {
        String sql = "insert into employee_profiles(user_id,name,position,department,hire_date,leave_date,status) values(?,?,?,?,?,?,?)";
        return executeUpdate(sql,
                employeeProfiles.getUserId(),
                employeeProfiles.getName(),
                employeeProfiles.getPosition(),
                employeeProfiles.getDepartment(),
                employeeProfiles.getHireDate(),
                employeeProfiles.getLeaveDate(),
                employeeProfiles.getStatus());
    }

    @Override
    public int deleteEmployee(int id) {
        String sql = "delete from employee_profiles where id=?";
        return executeUpdate(sql,id);
    }
}
